package com.happle.gcmclient.managers;

import java.util.ArrayList;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.widget.AutoCompleteTextView.Validator;

import com.happle.DAL.DBHelper;
import com.happle.gcmclient.config.Constants;
import com.happle.gcmclient.obj.ConversationObj;
import com.happle.gcmclient.obj.ConversationsNotSyncObj;
import com.happle.gcmclient.obj.LngObj;
import com.happle.gcmclient.obj.MessageObj;

public class MessagesManager {

	private DBHelper DBHelper;

	// Creates the helper, the DB connection
	public MessagesManager(Context context) {
		DBHelper = new DBHelper(context);
	}

	// Selects all the messages from one conversation out of the DB
	public ArrayList<MessageObj> getConversation(String wave_id) {
		// Creates an empty list
		ArrayList<MessageObj> lstConversation = new ArrayList<MessageObj>();

		// Gets a readable DB
		SQLiteDatabase db = DBHelper.getReadableDatabase();

		// Makes a select all query
		Cursor convCursor = db.rawQuery("SELECT * FROM "
				+ Constants.TBL_CONVERSATIONS + " WHERE " + Constants.wave_id_p
				+ "  = '" + wave_id + "'", null);

		// Runs over the lines in the table
		while (convCursor.moveToNext()) {
			// Adds it to the list
			lstConversation.add(convertCursorToMessageObj(convCursor));
		}
		// Closes the cursor & DB
		convCursor.close();
		db.close();

		// Returns the list
		return (lstConversation);
	}

	// Selects all the messages which not synced to server database
	public ArrayList<MessageObj> getAllMessageNotSend() {
		// Creates an empty list
		ArrayList<MessageObj> lstConversation = new ArrayList<MessageObj>();

		// Gets a readable DB
		SQLiteDatabase db = DBHelper.getReadableDatabase();

		// Makes a select all query
//		Cursor convCursor = db.rawQuery("SELECT * FROM "
//				+ Constants.TBL_CONVERSATIONS + " WHERE " + Constants.IS_SENT
//				+ "  == '0' AND " + Constants.MSG_STATUS +" == '1' AND " + Constants.is_owner_p + " == '1'", null);
        //temp
        Cursor convCursor = db.rawQuery("SELECT * FROM "
                + Constants.TBL_CONVERSATIONS + " WHERE " + Constants.MSG_STATUS +" == '1' AND "
                + Constants.is_owner_p + " == '1' AND " + Constants.is_sent_p + " == 0", null);
        //end temp

        // Runs over the lines in the table
		while (convCursor.moveToNext()) {
			// Adds it to the list
			lstConversation.add(convertCursorToMessageObj(convCursor));
		}
		// Closes the cursor & DB
		convCursor.close();
		db.close();

		// Returns the list
		return (lstConversation);
	}

	// Selects all the messages which closed to send to server
	public ArrayList<ConversationObj> getAllConversationsClosed() {
		ArrayList<ConversationObj> lstConversation = new ArrayList<ConversationObj>();
		SQLiteDatabase db = DBHelper.getReadableDatabase();
		Cursor convCursor = db.rawQuery("SELECT " + Constants.wave_id_p + ", "
				+ Constants.is_owner_p + ", " + Constants.lng_id_p + ", "
				+ Constants.msg_status_p + " FROM "
				+ Constants.TBL_CONVERSATIONS + " WHERE "
				+ Constants.msg_order_p + " = '0' AND "
				+ Constants.msg_status_p + " = '0' ORDER BY "
				+ Constants.datetime_p + " DESC", null);

		while (convCursor.moveToNext()) {
			// Takes the columns, saves each one into a parameter
			String _wave_id = convCursor.getString(0);
			boolean _is_owner = (convCursor.getInt(1) == 1) ? true : false;
			String _lng_id = convCursor.getString(2);
			boolean _conv_status = (convCursor.getInt(3) == 1) ? true : false;

			ConversationObj newConversationObj = new ConversationObj(null,
					_wave_id, _is_owner, null, _lng_id, null, _conv_status, 0);

			lstConversation.add(newConversationObj);
		}
		convCursor.close();
		db.close();

		return (lstConversation);
	}


	private MessageObj convertCursorToMessageObj(Cursor convCursor) {

		String _msg_id_local = convCursor.getString(0);
		String _wave_id = convCursor.getString(1);
		boolean _is_owner = (convCursor.getInt(2) == 1) ? true : false;
		String _message = convCursor.getString(3);
		String _lng_id = convCursor.getString(4);
		// //Create date from a timestamp (long value)

		Date _datetime = new Date(convCursor.getLong(5));

		boolean _msg_status = (convCursor.getInt(6) == 1) ? true : false;
		int _msg_order = convCursor.getInt(7);
		boolean _is_sent = (convCursor.getInt(8) == 1) ? true : false;
        boolean _is_new = (convCursor.getInt(9) == 1) ? true : false;
		// Creates the book with the parameters we created
		MessageObj newMessageObj = new MessageObj(_msg_id_local, _wave_id,
				_is_owner, _message, _lng_id, _datetime, _msg_status,
				_msg_order, _is_sent, _is_new);
		return newMessageObj;
	}

	public ConversationObj getConversationObj(String wave_id) {
		ConversationObj convObj = new ConversationObj();
		SQLiteDatabase db = DBHelper.getReadableDatabase();
		Cursor convCursor = db.rawQuery("SELECT " + Constants.msg_id_local_p
				+ ", " + Constants.wave_id_p + ", " + Constants.is_owner_p
				+ ", " + Constants.message_p + ", " + Constants.lng_id_p
                + ", " + Constants.datetime_p + ", " + Constants.msg_status_p
                + " FROM " + Constants.TBL_CONVERSATIONS + " WHERE "
				+ Constants.msg_order_p + " = '0' AND "
                + Constants.wave_id_p + " = '" + wave_id + "'", null);
		while (convCursor.moveToNext()) {
			// Takes the columns, saves each one into a parameter
			convObj.set_first_msg_id(convCursor.getString(0));
			convObj.set_wave_id(convCursor.getString(1));
			convObj.set_is_owner((convCursor.getInt(2) == 1) ? true : false);
			convObj.set_message(convCursor.getString(3));
			convObj.set_lng_id(convCursor.getString(4));
			convObj.set_datetime(new Date(convCursor.getLong(5)));
			convObj.set_msg_status((convCursor.getInt(6) == 1) ? true : false);
			int _msgs_count = 0;
			Cursor countCursor = db.rawQuery("SELECT count(*) FROM "
					+ Constants.TBL_CONVERSATIONS + " WHERE "
					+ Constants.wave_id_p + "='" + wave_id + "'", null);
			while (countCursor.moveToNext()) {
				_msgs_count = countCursor.getInt(0);
			}
			convObj.set_msgs_count(_msgs_count);
            countCursor.close();

            int _has_new_messages = 0;
            Cursor newMsgsCursor = db.rawQuery("SELECT count(*) FROM "
                    + Constants.TBL_CONVERSATIONS + " WHERE "
                    + Constants.is_new_p + " = 1 ", null);
            while (newMsgsCursor.moveToNext()) {
                _has_new_messages = newMsgsCursor.getInt(0);
            }
            convObj.set_has_new_messages(_has_new_messages == 1 ? true : false);
            newMsgsCursor.close();
		}
		convCursor.close();
		db.close();
		return convObj;
	}

	public int deleteConversation(String wave_id) {
		SQLiteDatabase db = DBHelper.getReadableDatabase();
		int res = db.delete(Constants.TBL_CONVERSATIONS, Constants.wave_id_p
				+ "=?", new String[] { wave_id });
		db.close();
		return res;
	}

	public int closeConversation(String wave_id) {
		SQLiteDatabase db = DBHelper.getReadableDatabase();
		ContentValues values = new ContentValues();
		values.put(Constants.msg_status_p, Constants.MSG_STATUS_CLOSED);
		int res = db.update(Constants.TBL_CONVERSATIONS, values,
				Constants.wave_id_p + "='" + wave_id + "'", null);

		db.close();
		return res;
	}

	public ArrayList<ConversationObj> getAllConversations() {
		ArrayList<ConversationObj> lstConversation = new ArrayList<ConversationObj>();
		SQLiteDatabase db = DBHelper.getReadableDatabase();
		// Cursor convCursor = db.query(Constants.TBL_CONVERSATIONS, null, null,
		// null, null, null, null);
		Cursor convCursor = db.rawQuery("SELECT " + Constants.msg_id_local_p
				+ ", " + Constants.wave_id_p + ", " + Constants.is_owner_p
				+ ", " + Constants.message_p + ", " + Constants.lng_id_p + ", "
				+ Constants.datetime_p + ", " + Constants.msg_status_p
				+ " FROM " + Constants.TBL_CONVERSATIONS + " WHERE "
				+ Constants.msg_order_p + " = '0'" + " ORDER BY "
				+ Constants.datetime_p + " DESC", null);
		while (convCursor.moveToNext()) {
			// Takes the columns, saves each one into a parameter
			String _first_msg_id = convCursor.getString(0);
			String _wave_id = convCursor.getString(1);
			boolean _is_owner = (convCursor.getInt(2) == 1) ? true : false;
			String _first_message = convCursor.getString(3);
			String _lng_id = convCursor.getString(4);
			// Create date from a timestamp (long value)
			Date _datetime = new Date(convCursor.getLong(5));
			boolean _conv_status = (convCursor.getInt(6) == 1) ? true : false;
			int _msgs_count = 0;
			Cursor countCursor = db.rawQuery("SELECT count(*) FROM "
					+ Constants.TBL_CONVERSATIONS + " WHERE "
					+ Constants.wave_id_p + "='" + _wave_id + "'", null);
			while (countCursor.moveToNext()) {
				_msgs_count = countCursor.getInt(0);
			}
            countCursor.close();

            int _has_new_messages = 0;
            Cursor newMsgsCursor = db.rawQuery("SELECT count(*) FROM "
                    + Constants.TBL_CONVERSATIONS + " WHERE "
                    + Constants.is_new_p + " = 1 ", null);
            while (newMsgsCursor.moveToNext()) {
                _has_new_messages = newMsgsCursor.getInt(0);
            }
            newMsgsCursor.close();

			ConversationObj newConversationObj = new ConversationObj(
					_first_msg_id, _wave_id, _is_owner, _first_message,
					_lng_id, _datetime, _conv_status, _msgs_count,(_has_new_messages == 1 ? true : false));

			lstConversation.add(newConversationObj);
		}
		convCursor.close();
		db.close();

		return (lstConversation);
	}

	// Add Message
	public MessageObj addMessage(MessageObj newMessage) {
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		// Verify if local_id exists
		if (verification(Constants.msg_id_local_p,
				newMessage.get_msg_id_local(), Constants.TBL_CONVERSATIONS, db)) {
			newMessage.set_msg_id_local(newMessage.get_msg_id_local()
					+ "_changed");
		}
		// Get message order by wave_id
		Cursor mCount = db.rawQuery("select count(*) from "
				+ Constants.TBL_CONVERSATIONS + " where " + Constants.wave_id_p
				+ " ='" + newMessage.get_wave_id() + "'", null);
		mCount.moveToFirst();
		int count = mCount.getInt(0);
		mCount.close();

		newMessage.set_msg_order(count);
		// Create a bag of values
		ContentValues values = new ContentValues();
		// Put values by column name
		values.put(Constants.wave_id_p, newMessage.get_wave_id());
		values.put(Constants.is_owner_p, (newMessage.is_is_owner() == true) ? 1
				: 0);
		values.put(Constants.message_p, newMessage.get_message());
		values.put(Constants.lng_id_p, newMessage.get_lng_id());
		// Convert date to timestamp (long value) then store into database
		values.put(Constants.datetime_p, newMessage.get_datetime().getTime()
				+ "");
		values.put(Constants.msg_status_p,
				(newMessage.is_msg_status() == true) ? 1 : 0);
		values.put(Constants.msg_id_local_p, newMessage.get_msg_id_local());
		values.put(Constants.msg_order_p, newMessage.get_msg_order());
		values.put(Constants.is_sent_p, (newMessage.is_is_sent() == true) ? 1
				: 0);
        values.put(Constants.is_new_p, (newMessage.is_is_new() == true) ? 1
                : 0);

		long insertId = db.insert(Constants.TBL_CONVERSATIONS, null, values);
		// Close the connection with the DB
		db.close();
		return newMessage;
	}

	// Update Message
	public int updateMessage(MessageObj newMessage) {
		// Get writable DB
		SQLiteDatabase db = DBHelper.getWritableDatabase();

		// Create a bag of values
		ContentValues values = new ContentValues();

		values.put(Constants.wave_id_p, newMessage.get_wave_id());
		values.put(Constants.is_owner_p, (newMessage.is_is_owner() == true) ? 1
				: 0);
		values.put(Constants.message_p, newMessage.get_message());
		values.put(Constants.lng_id_p, newMessage.get_lng_id());
		values.put(Constants.datetime_p, newMessage.get_datetime().getTime()
				+ "");
		values.put(Constants.msg_status_p,
				(newMessage.is_msg_status() == true) ? 1 : 0);
		values.put(Constants.msg_order_p, newMessage.get_msg_order());
		values.put(Constants.is_sent_p, (newMessage.is_is_sent() == true) ? 1
				: 0);
        values.put(Constants.is_new_p, (newMessage.is_is_new() == true) ? 1
                : 0);
		// Update the message
		int res = db.update(Constants.TBL_CONVERSATIONS, values,
				Constants.msg_id_local_p + "='" + newMessage.get_msg_id_local()
						+ "'", null);
		db.close();
		return res;
	}

    public int markNewMessage(MessageObj newMessage){
        SQLiteDatabase db = DBHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(Constants.is_new_p, newMessage.is_is_new());
        int res = db.update(Constants.TBL_CONVERSATIONS, values,
                Constants.msg_id_local_p + "='" + newMessage.get_msg_id_local()
                        + "'", null);
        db.close();
        return res;
    }

    public int readNewMessages(String wave_id) {
        SQLiteDatabase db = DBHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(Constants.is_new_p, 0);
        int res = db.update(Constants.TBL_CONVERSATIONS, values,
                Constants.wave_id_p + "='" + wave_id + "' and " +
                Constants.is_new_p + "= 1 ", null);
        db.close();
        return res;
    }

	public int deleteMessage(String msg_id_local) {
		SQLiteDatabase db = DBHelper.getReadableDatabase();
		int res = db.delete(Constants.TBL_CONVERSATIONS,
				Constants.msg_id_local_p + "=?", new String[] { msg_id_local });
		db.close();
        return res;
	}

	public boolean verification(String _key, String _value, String _table,
			SQLiteDatabase db) {
		Log.d("CheckExist", "Check exist with id : " + _value);
		Cursor c = db.rawQuery("SELECT 1 FROM " + _table + " WHERE " + _key
				+ "=?", new String[] { _value });
		boolean exists = c.moveToFirst();
		c.close();
		return exists;
	}

    public boolean saveNotSynchronizedConversation (ConversationsNotSyncObj convNotSyncObj) {
        SQLiteDatabase db = DBHelper.getWritableDatabase();
           Cursor c = db.rawQuery("SELECT " + Constants.conv_status_p + " FROM "
                    + Constants.TBL_CONVERSATIONS_NOT_SYNC + " WHERE "
                    + Constants.wave_id_p + "=?", new String[] { convNotSyncObj.get_wave_id() });
            c.moveToFirst();
            int status = c.getInt(0);
            c.close();
            if ( status == convNotSyncObj.get_conv_status())
                return true;

        ContentValues values = new ContentValues();
        values.put(Constants.wave_id_p, convNotSyncObj.get_wave_id());
        values.put(Constants.lng_id_p, convNotSyncObj.get_lng_id());
        values.put(Constants.is_owner_p, convNotSyncObj.is_is_owner());
        values.put(Constants.conv_status_p, convNotSyncObj.get_conv_status());

        long res = db.insert(Constants.TBL_CONVERSATIONS_NOT_SYNC, null, values);
        db.close();
        return  (res > 0);
    }

    public ArrayList<ConversationsNotSyncObj> getNotSynchronizedConversations() {
        ArrayList<ConversationsNotSyncObj> lstConversation = new ArrayList<ConversationsNotSyncObj>();
        SQLiteDatabase db = DBHelper.getReadableDatabase();
        Cursor convCursor = db.rawQuery("SELECT "
                + Constants.wave_id_p + ", "
                + Constants.lng_id_p + ", "
                + Constants.is_owner_p + ", "
                + Constants.conv_status_p + " FROM "
                + Constants.TBL_CONVERSATIONS_NOT_SYNC, null);
        while (convCursor.moveToNext()) {
            String _wave_id = convCursor.getString(0);
            String _lng_id = convCursor.getString(1);
            boolean _is_owner = (convCursor.getInt(2) == 1) ? true : false;
            int _conv_status = convCursor.getInt(3);

            ConversationsNotSyncObj newConversationObj = new ConversationsNotSyncObj(
                    _wave_id, _lng_id,_is_owner, _conv_status);

            lstConversation.add(newConversationObj);
        }
        convCursor.close();
    db.close();
    return (lstConversation);
}

    public void deleteSynchronizedConversations(ArrayList<ConversationsNotSyncObj> lstConversation) {
        SQLiteDatabase db = DBHelper.getReadableDatabase();
        final int size = lstConversation.size();
        for (int i=0; i<size; i++) {
            db.delete(Constants.TBL_CONVERSATIONS_NOT_SYNC, Constants.wave_id_p
            + "=" + lstConversation.get(i).get_wave_id() + " and " + Constants.conv_status_p + "=" + lstConversation.get(i).get_conv_status(), null);
        }
        db.close();
    }
}
